package com.ihr360.oneclick.dao;

import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetDto;
import com.ihr360.oneclick.dto.spreadsheet.SpreadsheetRowDto;
import com.ihr360.oneclick.entity.NotifyStateType;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;

/**
 * @author tom
 * @date 17-12-19
 */
public interface SpreadsheetRowDao extends JpaRepository<SpreadsheetRowDto, String>, JpaSpecificationExecutor<SpreadsheetRowDto> {

    List<SpreadsheetRowDto> findByCompanyIdAndTermIdAndStaffRecordId(String companyId, String termId, String staffRecordId);

    List<SpreadsheetRowDto> findByCompanyIdAndTermIdAndSpreadsheetId(String companyId, String termId, String spreadsheetId);

    @Query("select ssr from SpreadsheetRowDto ssr where ssr.spreadsheet.id = :spreadsheetId and ssr.id in (:idList) order by ssr.createdAt, ssr.rowIndex asc")
    List<SpreadsheetRowDto> findBySpreadsheetIdAndIdInOrderByCreatedAtRowIndexAsc(@Param("spreadsheetId") String spreadsheetId, @Param("idList") List<String> idList);

    List<SpreadsheetRowDto> findBySpreadsheetId(String spreadsheetId);

    @Query("select ssr from SpreadsheetRowDto ssr where ssr.spreadsheet.id = :spreadsheetId order by ssr.createdAt, ssr.rowIndex asc")
    List<SpreadsheetRowDto> findBySpreadsheetIdOrderByCreatedAtRowIndexAsc(@Param("spreadsheetId") String spreadsheetId);

    @Modifying
    @Query(value = "update SpreadsheetRowDto set weChatNoticeStatus = :noticeStatus where id = :id")
    int updateWeChatNoticeStatus(@Param("id") String id ,@Param("noticeStatus") NotifyStateType notifyStateType);

    @Modifying
    @Query(value = "update SpreadsheetRowDto set smsNoticeStatus = :noticeStatus where id = :id")
    int updateSMSNoticeStatus(@Param("id") String id ,@Param("noticeStatus") NotifyStateType notifyStateType);

    @Modifying
    @Query(value = "update SpreadsheetRowDto set mailNoticeStatus = :noticeStatus where id = :id")
    int updateEmailNoticeStatus(@Param("id") String id ,@Param("noticeStatus") NotifyStateType notifyStateType);

    List<SpreadsheetRowDto> findByIdIn(List<String> idList);

    List<SpreadsheetRowDto> findBySpreadsheetIdAndPublishStatus(String spreadsheetId, boolean publishStatus);

    long countByCompanyIdAndTermId(String companyId, String termId);

    @Modifying
    void deleteBySpreadsheetIdIn(List<String> spreadsheetIdList);

    void deleteBySpreadsheetId(String spreadsheetId);

    /**
     * 根据手机号获取最近6个月的工资信息
     *
     * @param mobileNo
     * @param beginDate
     * @param endDate
     * @return
     */
    List<SpreadsheetRowDto> findByMobileNoAndPublishStatusAndWeChatNoticeStatusAndMonthBetweenOrderByMonthDesc(String mobileNo, Boolean publishStatus, NotifyStateType notifyStateType, Date beginDate, Date endDate);

    /**
     * 根据手机号获取最近6个月的工资信息
     *
     * @param mobileNo
     * @param beginDate
     * @param endDate
     * @return
     */
    List<SpreadsheetRowDto> findByMobileNoAndPublishStatusAndMonthBetweenOrderByMonthDesc(String mobileNo, Boolean publishStatus, Date beginDate, Date endDate);

    /**
     * 统计阅读记录条数总数
     *
     * @param spreadsheetDto
     * @param isRead
     * @return
     */
    long countBySpreadsheetAndIsRead(SpreadsheetDto spreadsheetDto, Boolean isRead);

    /**
     * 获取微信发送状态
     * @param spreadsheetId
     * @param notifyStateType
     * @return
     */
    long countBySpreadsheetIdAndWeChatNoticeStatus(String spreadsheetId,NotifyStateType notifyStateType);

    /**
     * 获取短信发送状态
     * @param spreadsheetId
     * @param notifyStateType
     * @return
     */
    long countBySpreadsheetIdAndSmsNoticeStatus(String spreadsheetId,NotifyStateType notifyStateType);

    /**
     * 获取邮件发送状态
     * @param spreadsheetId
     * @param notifyStateType
     * @return
     */
    long countBySpreadsheetIdAndMailNoticeStatus(String spreadsheetId,NotifyStateType notifyStateType);

    List<SpreadsheetRowDto> findByCompanyIdAndMobileNoOrderByMonthDesc(String companyId, String mobileNo);

    /**
     * 根据id 和发布状态查询
     * @param id
     * @param publishStatus
     * @return
     */
    SpreadsheetRowDto findByIdAndPublishStatus(String id ,boolean publishStatus);

}
